# Author: tang
# Date: 2024-05-17
# Description: 新架构公共功能
# Previous version: 2.x
# Modification:
#     2024-11-19: 融合新架构3.0，修改部分数据库字段，图谱弃用mongodb存储，改用tdms文件存储

import os
# 标准库
import time
import math
import sqlite3
import logging
import traceback
import configparser
from binascii import unhexlify

# 第三方库
import redis
import crcmod
import pymysql


# logging.basicConfig(format='%(asctime)s-%(filename)s-[line:%(lineno)d]-%(levelname)s: %(message)s', level=logging.DEBUG)  # todo test

SAMPLING_RATE = 2000000  # 采样率
DOWN_SAMPLED_POINTS = 1000  # 降采样点数
STEP = SAMPLING_RATE // DOWN_SAMPLED_POINTS  # 降采样步长

conf = configparser.ConfigParser()
conf.read('config.ini')
# conf.read('settings.ini')
projectId = conf.get('RedisKeyPrefix', 'prefix')
PROJECT_ID = projectId.strip('"')
REDIS_CACHE_KEY = PROJECT_ID + ':cache_data'

MYSQL_CONFIG = dict(conf['MysqlConfig'])
REDIS_CONFIG = dict(conf['RedisConfig'])
BUSS_CONFIG = dict(conf['BusConfig'])


# current_dir = os.path.dirname(os.path.abspath(__file__))
# config_path = os.path.abspath(os.path.join(current_dir, "config.ini"))
# config = configparser.ConfigParser()
# config.read(config_path,encoding='utf-8')

# storage_conn = sqlite3.connect('storage_settings.db')  # 连接存储配置数据库
# sqlite_cursor = storage_conn.cursor()
# sqlite_cursor.execute('select * from storage_settings')
# data = sqlite_cursor.fetchone()
# columns_tuple = sqlite_cursor.description
# columns_list = [field_tuple[0] for field_tuple in columns_tuple]
# storage_conn.close()
# data_dict = dict(zip(columns_list, data))
# mysql_dsn = data_dict['mysql_dsn']  # mysql连接信息
# mysql_info = mysql_dsn.split(';')
# mysql_host = mysql_info[1].split('=')[1]  # mysql连接主机
# mysql_user = mysql_info[2].split('=')[1]  # mysql连接用户
# mysql_pwd = mysql_info[3].split('=')[1]  # mysql连接密码
# mysql_db = mysql_info[4].split('=')[1]  # mysql连接数据库
# mysql_port = int(mysql_info[5].split('=')[1])  # mysql连接端口
# redis_host = data_dict['redis_host']  # redis连接主机
# redis_port = int(data_dict['redis_port'])  # redis连接端口
# redis_pwd = data_dict['redis_pwd']  # redis连接密码
#tdms_save_path = data_dict['tdms_save_path']  # tdms文件保存位置
# is_store_mongodb = data_dict['is_store_mongodb']  # 是否存储mongodb
# is_store_tdms = data_dict['is_store_tdms']  # 是否存储tdms

#数据库连接
# mysql_host='192.168.0.50'
# mysql_port=3306
# mysql_user='root'
# mysql_pwd='123456'
# mysql_db='django-vue3-admin'
# db_type = mysql

#redis
# redis_host = '192.168.0.50'
# redis_port = 9736
# redis_pwd = '123456'
# redis_db = 0

#tdms存储路径
# tdms_save_path = 'D:\\datas'

# #
# is_store_mongodb=1
# is_store_tdms=0


def mysql_connect():
    while True:
        try:
            mysql_conn = pymysql.connect(host=MYSQL_CONFIG['host'], port=int(MYSQL_CONFIG['port']), 
                                         user=MYSQL_CONFIG['username'], passwd=MYSQL_CONFIG['password'],
                                         database=MYSQL_CONFIG['database'])
            logging.info('mysql connect success')
            break
        except Exception as e:
            logging.error(traceback.format_exc())
            time.sleep(60)
            logging.info('mysql reconnect')
    return mysql_conn


def redis_connect():
    while True:
        redis_conn = redis.StrictRedis(host=REDIS_CONFIG['host'], port=int(REDIS_CONFIG['port']), 
                                       db=int(REDIS_CONFIG['db']), password=REDIS_CONFIG['password'])
        try:
            redis_conn.ping()
            logging.info('redis connect success')
            break
        except Exception as e:
            logging.error(traceback.format_exc())
            redis_conn.close()
            time.sleep(60)
            logging.info('redis reconnect')
    return redis_conn


def sqlite_connect():
    sqlite_conn = sqlite3.connect('settings.db')  # 采集库
    return sqlite_conn


def crc16_count(hex_data):
    """
    CRC16计算
    Args:
        hex_data: 十六进制数据

    Returns: CRC16校验码

    """
    crc16 = crcmod.mkCrcFun(0x18005, rev=True, initCrc=0xFFFF, xorOut=0x0000)
    data = hex_data.replace(" ", "")
    result_data = hex(crc16(unhexlify(data))).upper()
    str_list = list(result_data)
    if len(str_list) < 6:
        str_list.insert(2, '0' * (6 - len(str_list)))  # 位数不足补0
    crc_data = "".join(str_list)
    return crc_data


def modbus_command(dev_id, function_code, address_h, address_l, byte_count_h, byte_count_l):
    """
    modbus指令拼接
    Args:
        dev_id: 设备ID
        function_code: 功能码
        address_h: 地址高位
        address_l: 地址低位
        byte_count_h: 字节数高位
        byte_count_l: 字节数低位

    Returns: modbus指令

    """
    command_array = [0x01, 0x03, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00]
    command_array[0] = dev_id
    command_array[1] = function_code
    command_array[2] = address_h
    command_array[3] = address_l
    command_array[4] = byte_count_h
    command_array[5] = byte_count_l
    crc_result = crc16_count(bytes(command_array)[0:(len(command_array) - 2)].hex())
    command_array[6] = int(crc_result[4:], 16)
    command_array[7] = int(crc_result[2:4], 16)
    return bytes(command_array)


def modbus_command_10(dev_id, function_code, address_h, address_l, regis_count_h, regis_count_l, byte_count, data_1_h,
                      data_1_l, data_2_h, data_2_l):
    """
    modbus_10功能码指令拼接
    Args:
        dev_id: 设备ID
        function_code: 功能码
        address_h: 起始地址高位
        address_l: 起始地址低位
        regis_count_h: 写入寄存器的个数高位
        regis_count_l: 写入寄存器的个数低位
        byte_count: 字节数
        data_1_h: 数据1高位
        data_1_l: 数据1低位
        data_2_h: 数据2高位
        data_2_l: 数据2低位

    Returns: modbus指令

    """
    # 01 10 0009 0002 04 xxxx xxxx
    command_array = [0x01, 0x10, 0x00, 0x09, 0x00, 0x02, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00]
    command_array[0] = dev_id
    command_array[1] = function_code
    command_array[2] = address_h
    command_array[3] = address_l
    command_array[4] = regis_count_h
    command_array[5] = regis_count_l
    command_array[6] = byte_count
    command_array[7] = data_1_h
    command_array[8] = data_1_l
    command_array[9] = data_2_h
    command_array[10] = data_2_l
    crc_result = crc16_count(bytes(command_array)[0:(len(command_array) - 2)].hex())
    command_array[11] = int(crc_result[4:], 16)
    command_array[12] = int(crc_result[2:4], 16)
    return bytes(command_array)


def modbus_command_14(dev_id, file_h, file_l, start_h, start_l, len_h, len_l):
    """
    modbus_14功能码指令拼接
    Args:
        dev_id: 设备ID
        file_h: 文件号高位
        file_l: 文件号低位
        start_h: 起始号高位
        start_l: 起始号低位
        len_h: 数据长度高位
        len_l: 数据长度低位

    Returns: modbus指令

    """
    # 01 14 07 06 0001 0000 007b
    command_array = [0x01, 0x14, 0x07, 0x06, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00]
    command_array[0] = dev_id
    command_array[4] = file_h
    command_array[5] = file_l
    command_array[6] = start_h
    command_array[7] = start_l
    command_array[8] = len_h
    command_array[9] = len_l
    crc_result = crc16_count(bytes(command_array)[0:(len(command_array) - 2)].hex())
    command_array[10] = int(crc_result[4:], 16)
    command_array[11] = int(crc_result[2:4], 16)
    return bytes(command_array)


def crc_check(crc_data):
    """
    CRC16校验
    Args:
        crc_data: 被校验数组

    Returns: 校验结果

    """
    crc_rcv_value = int.from_bytes(crc_data[(len(crc_data) - 2): len(crc_data)], byteorder='little')
    crc_count_value = int(crc16_count(crc_data[0: (len(crc_data) - 2)].hex()), 16)
    if crc_rcv_value == crc_count_value:
        crc_result = True
    else:
        crc_result = False
    return crc_result


def data_receive(sock):
    """
    Args:
        sock: socket对象

    Returns: 接收到的数据

    """
    time.sleep(0.2)  # 添加延时避免发送接收过快
    rcv_bytes_data = bytes()  # 定义bytes()类型变量
    n = 1
    crc_flag = False
    while True:
        try:
            rcv_data = sock.recv(1024)
            # logging.debug((sock, ''.join(['%02X ' % b for b in rcv_data])))
            if rcv_data:
                rcv_bytes_data = rcv_bytes_data + rcv_data
            else:
                break
            if crc_check(rcv_bytes_data) or len(rcv_bytes_data) == int.from_bytes(rcv_bytes_data[5:7],
                                                                                  byteorder='big'):
                crc_flag = True
                break
        except Exception as e:
            rcv_bytes_data = bytes()  # 异常先清空接收变量
            logging.error(traceback.format_exc())
            n += 1
            if n > 3:  # 异常3次直接退出
                break
            continue

    if not crc_flag:
        rcv_bytes_data = bytes()

    return rcv_bytes_data


def mysql_query_tables(db_name, cursor):
    """
    mysql查询数据表名
    Args:
        db_name: mysql库名
        cursor: mysql游标

    Returns: 表名列表

    """
    sql = 'select table_name from information_schema.tables where table_schema=%s order by table_name;'
    val = [db_name]
    cursor.execute(sql, val)
    tbl_data = cursor.fetchall()
    tbl_list = [i[0] for i in tbl_data]
    logging.debug(tbl_list)
    return tbl_list


def mysql_create_data_tables(db_name, cursor):
    """
    mysql创建数据表
    Args:
        db_name: mysql库名
        cursor: mysql游标

    Returns: None

    """
    tbl_list = mysql_query_tables(db_name, cursor)
    cursor.execute('select distinct(device_code),channel_type,channel_number from channel_register')
    res = cursor.fetchall()
    logging.debug(res)
    for i in res:
        device_type = i[0][2:4]
        device_code = i[0]
        channel_type = i[1]
        channel_number = i[2]
        table_name = 'data_%s_%s_%s' % (device_code, channel_number, channel_type)
        if table_name not in tbl_list:
            sql = 'select now()'
            if device_type != '27' and channel_type == 'temp':  # 温度 todo del channel_number
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                          `id` int NOT NULL AUTO_INCREMENT,
                          `temperature` float DEFAULT NULL,
                          `alarm` tinyint DEFAULT NULL,
                          `create_time` timestamp NULL DEFAULT NULL,
                          `is_abnormal` tinyint DEFAULT NULL,
                          PRIMARY KEY (`id`) USING BTREE,
                          KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
                cursor.execute(sql)

            if device_type in {'01', '02'}:  # 八通道或三通道 todo del channel_number
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                          `id` int NOT NULL AUTO_INCREMENT,
                          `amplitude` float DEFAULT NULL,
                          `frequency` int DEFAULT NULL,
                          `energy` float DEFAULT NULL,
                          `alarm` tinyint DEFAULT NULL,
                          `create_time` timestamp NULL DEFAULT NULL,
                          `tdms_file_path` varchar(255),
                          `is_abnormal` tinyint DEFAULT NULL,
                          PRIMARY KEY (`id`) USING BTREE,
                          KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif device_type == '04':
                if channel_type == 'aa':  #  todo del aa prefix
                    sql = """CREATE TABLE IF NOT EXISTS `%s` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `amplitude` float DEFAULT NULL,
                              `mean_value` float DEFAULT NULL,
                              `noise_mean` float DEFAULT NULL,
                              `frequency` int DEFAULT NULL,
                              `pulse_cycle` int DEFAULT NULL,
                              `50Hz` float DEFAULT NULL,
                              `100Hz` float DEFAULT NULL,
                              `alarm` tinyint DEFAULT NULL,
                              `forewarning` tinyint DEFAULT NULL,
                              `effective_value` float DEFAULT NULL,
                              `noise_level` float DEFAULT NULL,
                              `status` tinyint DEFAULT NULL,
                              `create_time` timestamp NULL DEFAULT NULL,
                              `is_abnormal` tinyint DEFAULT NULL,
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
                elif channel_type == 'tev':  #  todo del tev prefix
                    sql = """CREATE TABLE IF NOT EXISTS `%s` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `amplitude` float DEFAULT NULL,
                              `mean_value` float DEFAULT NULL,
                              `noise_mean` float DEFAULT NULL,
                              `frequency` int DEFAULT NULL,
                              `pulse_cycle` int DEFAULT NULL,
                              `50Hz` float DEFAULT NULL,
                              `100Hz` float DEFAULT NULL,
                              `alarm` tinyint DEFAULT NULL,
                              `forewarning` tinyint DEFAULT NULL,
                              `effective_value` float DEFAULT NULL,
                              `noise_level` float DEFAULT NULL,
                              `status` tinyint DEFAULT NULL,
                              `create_time` timestamp NULL DEFAULT NULL,
                              `is_abnormal` tinyint DEFAULT NULL,
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif device_type == '12':  # todo del channel_number
                sql = """CREATE TABLE `%s` (
                          `id` int NOT NULL AUTO_INCREMENT,
                          `cc` float DEFAULT NULL,
                          `alarm` tinyint DEFAULT NULL,
                          `create_time` timestamp NULL DEFAULT NULL,
                          `is_abnormal` tinyint DEFAULT NULL,
                          PRIMARY KEY (`id`) USING BTREE,
                          KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif device_type == '27':
                if channel_type == 'voltage':
                    sql = """CREATE TABLE IF NOT EXISTS `%s` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `voltage` float DEFAULT NULL,
                              `alarm` tinyint DEFAULT NULL,
                              `create_time` timestamp NULL DEFAULT NULL,
                              `is_abnormal` tinyint DEFAULT NULL,
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
                elif channel_type == 'tev':  # todo peak_value->amplitude
                    sql = """CREATE TABLE IF NOT EXISTS `%s` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `amplitude` float DEFAULT NULL,
                              `discharge_average` float DEFAULT NULL,
                              `noise_level` float DEFAULT NULL,
                              `pulse_per_cycle` int DEFAULT NULL,
                              `frequency` int DEFAULT NULL,
                              `50hz` float DEFAULT NULL,
                              `100hz` float DEFAULT NULL,
                              `warning` tinyint DEFAULT NULL,
                              `alarm` tinyint DEFAULT NULL,
                              `create_time` timestamp NULL DEFAULT NULL,
                              `is_abnormal` tinyint DEFAULT NULL,
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
                elif channel_type == 'temp':  # todo del channel_number
                    sql = """CREATE TABLE IF NOT EXISTS `%s` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `temperature` float DEFAULT NULL,
                              `warning` tinyint DEFAULT NULL,
                              `alarm` tinyint DEFAULT NULL,
                              `diff_warning` tinyint DEFAULT NULL,
                              `diff_alarm` tinyint DEFAULT NULL,
                              `create_time` timestamp NULL DEFAULT NULL,
                              `is_abnormal` tinyint DEFAULT NULL,
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
                elif channel_type == 'water':
                    sql = """CREATE TABLE IF NOT EXISTS `%s` (
                              `id` int NOT NULL AUTO_INCREMENT,
                              `water_status` tinyint DEFAULT NULL,
                              `create_time` timestamp NULL DEFAULT NULL,
                              `is_abnormal` tinyint DEFAULT NULL,
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name

            cursor.execute(sql)

    logging.info('mysql create data tables success')
#创建单独表
def mysql_create_alone_data_tables(table_name,device_type,channel_type):
    """
    mysql创建数据表
    Returns: None

    """
    mysql_conn=mysql_connect()
    with mysql_conn.cursor() as cursor:
        sql = 'select now()'
        if device_type != '27' and channel_type == 'temp':  # 温度 todo del channel_number
            sql = """CREATE TABLE IF NOT EXISTS `%s` (
                        `id` int NOT NULL AUTO_INCREMENT,
                        `temperature` float DEFAULT NULL,
                        `alarm` tinyint DEFAULT NULL,
                        `create_time` timestamp NULL DEFAULT NULL,
                        `is_abnormal` tinyint DEFAULT NULL,
                        PRIMARY KEY (`id`) USING BTREE,
                        KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            cursor.execute(sql)

        if device_type in {'01', '02'}:  # 八通道或三通道 todo del channel_number
            sql = """CREATE TABLE IF NOT EXISTS `%s` (
                        `id` int NOT NULL AUTO_INCREMENT,
                        `amplitude` float DEFAULT NULL,
                        `frequency` int DEFAULT NULL,
                        `energy` float DEFAULT NULL,
                        `alarm` tinyint DEFAULT NULL,
                        `create_time` timestamp NULL DEFAULT NULL,
                        `tdms_file_path` varchar(255),
                        `is_abnormal` tinyint DEFAULT NULL,
                        PRIMARY KEY (`id`) USING BTREE,
                        KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
        elif device_type == '04':
            if channel_type == 'aa':  #  todo del aa prefix
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                            `id` int NOT NULL AUTO_INCREMENT,
                            `amplitude` float DEFAULT NULL,
                            `mean_value` float DEFAULT NULL,
                            `noise_mean` float DEFAULT NULL,
                            `frequency` int DEFAULT NULL,
                            `pulse_cycle` int DEFAULT NULL,
                            `50Hz` float DEFAULT NULL,
                            `100Hz` float DEFAULT NULL,
                            `alarm` tinyint DEFAULT NULL,
                            `forewarning` tinyint DEFAULT NULL,
                            `effective_value` float DEFAULT NULL,
                            `noise_level` float DEFAULT NULL,
                            `status` tinyint DEFAULT NULL,
                            `create_time` timestamp NULL DEFAULT NULL,
                            `is_abnormal` tinyint DEFAULT NULL,
                            PRIMARY KEY (`id`) USING BTREE,
                            KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif channel_type == 'tev':  #  todo del tev prefix
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                            `id` int NOT NULL AUTO_INCREMENT,
                            `amplitude` float DEFAULT NULL,
                            `mean_value` float DEFAULT NULL,
                            `noise_mean` float DEFAULT NULL,
                            `frequency` int DEFAULT NULL,
                            `pulse_cycle` int DEFAULT NULL,
                            `50Hz` float DEFAULT NULL,
                            `100Hz` float DEFAULT NULL,
                            `alarm` tinyint DEFAULT NULL,
                            `forewarning` tinyint DEFAULT NULL,
                            `effective_value` float DEFAULT NULL,
                            `noise_level` float DEFAULT NULL,
                            `status` tinyint DEFAULT NULL,
                            `create_time` timestamp NULL DEFAULT NULL,
                            `is_abnormal` tinyint DEFAULT NULL,
                            PRIMARY KEY (`id`) USING BTREE,
                            KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
        elif device_type == '12':  # todo del channel_number
            sql = """CREATE TABLE `%s` (
                        `id` int NOT NULL AUTO_INCREMENT,
                        `cc` float DEFAULT NULL,
                        `alarm` tinyint DEFAULT NULL,
                        `create_time` timestamp NULL DEFAULT NULL,
                        `is_abnormal` tinyint DEFAULT NULL,
                        PRIMARY KEY (`id`) USING BTREE,
                        KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
        elif device_type == '27':
            if channel_type == 'voltage':
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                            `id` int NOT NULL AUTO_INCREMENT,
                            `voltage` float DEFAULT NULL,
                            `alarm` tinyint DEFAULT NULL,
                            `create_time` timestamp NULL DEFAULT NULL,
                            `is_abnormal` tinyint DEFAULT NULL,
                            PRIMARY KEY (`id`) USING BTREE,
                            KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif channel_type == 'tev':  # todo peak_value->amplitude
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                            `id` int NOT NULL AUTO_INCREMENT,
                            `amplitude` float DEFAULT NULL,
                            `discharge_average` float DEFAULT NULL,
                            `noise_level` float DEFAULT NULL,
                            `pulse_per_cycle` int DEFAULT NULL,
                            `frequency` int DEFAULT NULL,
                            `50hz` float DEFAULT NULL,
                            `100hz` float DEFAULT NULL,
                            `warning` tinyint DEFAULT NULL,
                            `alarm` tinyint DEFAULT NULL,
                            `create_time` timestamp NULL DEFAULT NULL,
                            `is_abnormal` tinyint DEFAULT NULL,
                            PRIMARY KEY (`id`) USING BTREE,
                            KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif channel_type == 'temp':  # todo del channel_number
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                            `id` int NOT NULL AUTO_INCREMENT,
                            `temperature` float DEFAULT NULL,
                            `warning` tinyint DEFAULT NULL,
                            `alarm` tinyint DEFAULT NULL,
                            `diff_warning` tinyint DEFAULT NULL,
                            `diff_alarm` tinyint DEFAULT NULL,
                            `create_time` timestamp NULL DEFAULT NULL,
                            `is_abnormal` tinyint DEFAULT NULL,
                            PRIMARY KEY (`id`) USING BTREE,
                            KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name
            elif channel_type == 'water':
                sql = """CREATE TABLE IF NOT EXISTS `%s` (
                            `id` int NOT NULL AUTO_INCREMENT,
                            `water_status` tinyint DEFAULT NULL,
                            `create_time` timestamp NULL DEFAULT NULL,
                            `is_abnormal` tinyint DEFAULT NULL,
                            PRIMARY KEY (`id`) USING BTREE,
                            KEY `idx_create_time` (`create_time`) USING BTREE)""" % table_name

        cursor.execute(sql)
    cursor.close()
    logging.info('mysql: The data table exist or create alone data tables success')



def mysql_create_para_tables(db_name, cursor, table_name):
    """
    mysql创建参数表
    Args:
        db_name: mysql库名
        cursor: mysql游标
        table_name: 参数表名

    Returns: None

    """
    tbl_list = mysql_query_tables(db_name, cursor)
    if table_name not in tbl_list:
        sql = """CREATE TABLE IF NOT EXISTS `%s` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `device_code` varchar(20),
                  `parameter` varchar(3500),
                  `para_type` varchar(20),
                  `is_locked` tinyint(1) DEFAULT NULL,
                  `is_del` tinyint(1) DEFAULT NULL,
                  `create_time` timestamp NULL DEFAULT NULL,
                  PRIMARY KEY (`id`) USING BTREE
                )""" % table_name
        cursor.execute(sql)
    logging.info('mysql create para tables success')


def calculate_correlation(amp_list, phase_list):
    """
    计算50Hz和100Hz相关性
    Args:
        amp_list: 计算后的幅值数组
        phase_list: 原相位数组

    Returns: 50Hz和100Hz相关性之和

    """
    corre_res = 1
    try:
        cal_amp_list = amp_list
        org_phase_list = phase_list

        org_phase_list_new = []  # 过滤后的原始相位数组
        if cal_amp_list:
            amp_v1 = (sum(cal_amp_list) / len(cal_amp_list)) * 0.2
            for i in range(len(cal_amp_list)):
                if cal_amp_list[i] > amp_v1:
                    org_phase_list_new.append(org_phase_list[i])
        logging.debug((org_phase_list_new, len(org_phase_list_new)))

        if org_phase_list_new:
            point_list = [i for i in range(0, SAMPLING_RATE + 1, STEP)]
            logging.debug((point_list, len(point_list)))

            temp_dict = {}
            for i in range(len(point_list) - 1):
                key = str(point_list[i]) + '_' + str(point_list[i + 1])
                temp_dict[key] = []
            logging.debug(temp_dict)

            for i in range(len(org_phase_list_new)):
                for j in range(len(point_list) - 1):
                    if point_list[j] <= org_phase_list_new[i] < point_list[j + 1]:
                        key = str(point_list[j]) + '_' + str(point_list[j + 1])
                        temp_dict[key].append(org_phase_list_new[i])
            logging.debug(temp_dict)

            dict_values = temp_dict.values()
            logging.debug((dict_values, len(dict_values)))
            res_list = []
            for i in dict_values:
                if i:
                    res_list.append(max(i))
                else:
                    res_list.append(0)
            logging.debug(res_list)
            logging.debug(len(res_list))

            offset_2 = int(DOWN_SAMPLED_POINTS * 0.02)
            offset_1 = int(DOWN_SAMPLED_POINTS * 0.01)
            offset_05 = int(DOWN_SAMPLED_POINTS * 0.005)

            # 偏移2%
            amp_0_2 = res_list[:DOWN_SAMPLED_POINTS - offset_2]
            amp_2_0 = res_list[offset_2:]
            # 偏移1%
            amp_0_1 = res_list[:DOWN_SAMPLED_POINTS - offset_1]
            amp_1_0 = res_list[offset_1:]
            # 偏移0.5%
            amp_0_5 = res_list[:DOWN_SAMPLED_POINTS - offset_05]
            amp_5_0 = res_list[offset_05:]

            amp_2 = [x * y for x, y in zip(amp_0_2, amp_2_0)]
            amp_1 = [x * y for x, y in zip(amp_0_1, amp_1_0)]
            amp_05 = [x * y for x, y in zip(amp_0_5, amp_5_0)]
            logging.debug(amp_2)
            logging.debug(amp_1)
            logging.debug(amp_05)

            res_2 = math.sqrt(abs(sum(amp_2) / (DOWN_SAMPLED_POINTS - offset_2)))
            res_1 = math.sqrt(abs(sum(amp_1) / (DOWN_SAMPLED_POINTS - offset_1)))
            res_05 = math.sqrt(abs(sum(amp_05) / (DOWN_SAMPLED_POINTS - offset_05)))

            avg_val = sum(res_list) / DOWN_SAMPLED_POINTS
            if avg_val == 0:
                avg_val = 1
            res_50hz = abs((res_2 - res_1) / avg_val)  # 50Hz相关性
            res_100hz = abs((res_1 - res_05) / avg_val)  # 100Hz相关性
            logging.debug((res_50hz, res_100hz))
            corre_res = res_50hz + res_100hz
            corre_res = 1  # todo test
    except Exception as e:
        logging.error(traceback.format_exc())

    return corre_res


def int_to_int16(value):
    """int转有符号16进制"""
    if value >= 32768:
        value -= 65536
    return value


def atlas_read_04(sock, slave_id, frequency, file_h, file_l):
    """
    04设备14功能码读取图谱
    Args:
        sock: socket对象
        slave_id: 设备ID
        frequency: 频次
        file_h: 文件号高位
        file_l: 文件号低位

    Returns: 图谱数据

    """
    atlas_data = bytes()

    record_num = 1 + frequency * 2  # 1 + 频次 * (幅值（1个记录号） + 相位（1个记录号）)
    logging.debug(record_num)
    count = math.ceil(record_num / 123)
    for i in range(count):
        addr = i * 123
        addr_byte = addr.to_bytes(2, byteorder='big')
        start_h = addr_byte[0]
        start_l = addr_byte[1]
        len_h = 0
        if i == count - 1:
            len_l = record_num - (i * 123)
        else:
            len_l = 123

        atlas_cmd = modbus_command_14(slave_id, file_h, file_l, start_h, start_l, len_h, len_l)
        logging.debug("atlas_cmd:" + ''.join(['%02X ' % i for i in atlas_cmd]))
        sock.send(atlas_cmd)

        time.sleep(0.2)  # 添加延时避免发送接收过快
        rcv_bytes_data = bytes()  # 定义bytes()类型变量
        n = 1
        crc_flag = False
        while True:
            try:
                rcv_data = sock.recv(1024)
                # logging.debug((sock, ''.join(['%02X ' % b for b in rcv_data])))
                if rcv_data:
                    rcv_bytes_data = rcv_bytes_data + rcv_data
                else:
                    break
                if int.from_bytes(rcv_bytes_data[1:2], byteorder='big') == 20 and len(rcv_bytes_data) == (
                        int.from_bytes(rcv_bytes_data[2:3], byteorder='big') + 5):
                    logging.debug(''.join(['%02X ' % b for b in rcv_bytes_data]))
                    if i == 0:
                        atlas_data = atlas_data + rcv_bytes_data[7:-2]  # 只有第一包记录号0有2个字节的点数
                    else:
                        atlas_data = atlas_data + rcv_bytes_data[5:-2]

                    crc_flag = True
                    break
            except Exception as e:
                rcv_bytes_data = bytes()  # 异常先清空接收变量
                logging.error(traceback.format_exc())
                n += 1
                if n > 3:  # 异常3次直接退出
                    break
                continue

        if not crc_flag:
            atlas_data = bytes()

    return atlas_data


def ensure_table_exists(cursor, table_name, keys):
    # 检查表是否存在
    check_table_sql = f"""
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = '{table_name}'
    """
    cursor.execute(check_table_sql)
    table_exists = cursor.fetchone()[0] == 1

    if not table_exists:
        # 构建创建表的 SQL 语句
        columns = ['id INT AUTO_INCREMENT PRIMARY KEY']
        indexes = []

        for key in keys:
            # 假设所有字段类型为 VARCHAR(100)，可以根据需要调整
            if key == 'create_time':
                columns.append(f'{key} timestamp')
                indexes.append(f'KEY `idx_{key}` (`{key}`) USING BTREE')
            else:
                columns.append(f'{key} VARCHAR(100)')

        columns_definition = ', '.join(columns)
        indexes_definition = ', '.join(indexes)

        if indexes_definition:
            create_table_sql = f"""
            CREATE TABLE {table_name} (
                {columns_definition},
                {indexes_definition}
            )
            """
        else:
            create_table_sql = f"""
            CREATE TABLE {table_name} (
                {columns_definition}
            )
            """

        cursor.execute(create_table_sql)
        logging.debug(f"Table {table_name} created successfully.")
    else:
        logging.debug(f"Table {table_name} already exists.")
